package com.boot.ys.mybatisplus.service.impl;

import com.boot.ys.mybatisplus.entity.Excel;
import com.boot.ys.mybatisplus.mapper.ExcelMapper;
import com.boot.ys.mybatisplus.service.ExcelService;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

@Service
public class ExcelServiceImpl implements ExcelService {


    private Logger log = LoggerFactory.getLogger(ExcelServiceImpl.class);

    @Autowired
    private ExcelMapper excelMapper;


    /**
     * 导入POI
     * @param file
     */
    @Override
    public boolean readExcelPoi(MultipartFile file){
        String fileName = file.getOriginalFilename();
        boolean notNull = false;
        List<Excel> userList = new ArrayList<>();
        if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
            System.out.println("上传文件格式不正确");
        }
        boolean isExcel2003 = true;
        if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
            isExcel2003 = false;
        }
        InputStream is = null;
        try {
            is = file.getInputStream();
        } catch (IOException e) {
            e.printStackTrace();
        }
        Workbook wb = null;

        if (isExcel2003) {
            try {
                wb = new HSSFWorkbook(is);
            } catch (IOException e) {
                e.printStackTrace();
            }
        } else {
            try {
                wb = new XSSFWorkbook(is);
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        Sheet sheet = wb.getSheetAt(0);
        if(sheet!=null){
            notNull = true;
        }
        Excel excel;
        int lastRowNum = sheet.getLastRowNum();
        for (int r = 1; r <= sheet.getLastRowNum(); r++) {//r = 2 表示从第三行开始循环 如果你的第三行开始是数据
            Row row = sheet.getRow(r);//通过sheet表单对象得到 行对象
            if (row == null){
                continue;
            }
            excel = new Excel();
           //得到每一行第一个单元格的值
            String color = null;
            if(row.getCell(0) != null){
                Cell cell = row.getCell(0);
                cell.setCellType(Cell.CELL_TYPE_STRING);
                color = cell.getStringCellValue();
            }

            //得到每一行的 第二个单元格的值
            String createDate = null;
            if(row.getCell(1) != null){
                //这样子写会 有问题，如果输入的是数字就会报错，getStringCellValue只会读取字符串
                //createDate = row.getCell(1).getStringCellValue();
                Cell cell = row.getCell(1);
                //这行代码的意思是
                //excel导入如果是数字类型的poi的cell.getStringCellValue()会报错，先将cell转化为文本类型再进行读取
                cell.setCellType(Cell.CELL_TYPE_STRING);
                createDate = cell.getStringCellValue();
            }

            //得到每一行的 第三个单元格的值,这里使用if判断，Excel里面的内容可以为空，但是要注意数据库里面这个字段是否允许为空
            String note = null;
            if (row.getCell(2) != null){
                Cell cell = row.getCell(2);
                cell.setCellType(Cell.CELL_TYPE_STRING);
                note = cell.getStringCellValue();
                //如果是日期类型可以这样子处理，不处理的话插入数据库里面会变成一串数字
//                if (HSSFDateUtil.isCellDateFormatted(cell)){
//                    Date date = cell.getDateCellValue();
//                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
//                    note = sdf.format(date);
//                }
            }
            //完整的循环一次 就组成了一个对象
            excel.setColor(color);
            excel.setNote(note);

            userList.add(excel);
        }
        for (Excel e : userList){
            e.setCreateDate(new Date());
            excelMapper.insert(e);
        }
        return notNull;
    }

    /**
     * 导出POI
     */
    @Override
    public void exportExcel(HttpServletResponse response) {
        List<Excel> excelsList = excelMapper.selectList(null);

        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet("获取excel测试表格");
        HSSFRow row = null;

        row = sheet.createRow(0);
        row.createCell(0).setCellValue("颜色");//为第二个单元格设值
        row.createCell(1).setCellValue("创建时间");//为第三个单元格设值
        row.createCell(2).setCellValue("备注");//为第四个单元格设值

        for (int i = 0; i < excelsList.size(); i++) {
            row = sheet.createRow(i + 1);
            Excel excel = excelsList.get(i);
            row.createCell(0).setCellValue(excel.getColor());
            row.createCell(1).setCellValue(String.valueOf(excel.getCreateDate()));
            row.createCell(2).setCellValue(excel.getNote());
        }
        sheet.setDefaultRowHeight((short) (16.5 * 20));
        //列宽自适应
        for (int i = 0; i <= 13; i++) {
            sheet.autoSizeColumn(i);
        }
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        OutputStream os = null;
        try {
            os = response.getOutputStream();
            String filename = "测试";
            response.setHeader("Content-disposition", "attachment;filename="+new String(filename.getBytes("gb2312"), "ISO8859-1")+".xls");//默认Excel名称
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            try {
                wb.write(os);
                os.flush();
                os.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

    }




}
